/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package edu.uoc.tdp.pac4.servidor;

import edu.uoc.tdp.pac4.beans.Usuari;
import edu.uoc.tdp.pac4.beans.Taller;
import edu.uoc.tdp.pac4.beans.Perfil;
import edu.uoc.tdp.pac4.comun.GestorConexionInterface;
import edu.uoc.tdp.pac4.i18n.TDSLanguageUtils;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.Serializable;
import java.rmi.RemoteException;
import java.rmi.server.UnicastRemoteObject;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Arrays;

/**
 *
 * @author andres
 */
public class GestorConexionImpl  extends UnicastRemoteObject implements GestorConexionInterface, Serializable {
    
    private static final long serialVersionUID = 1L;
    public Connection conexion;
    //final String propertiesFile = new File("").getAbsolutePath() + "//src//properties///configuration.properties";
    /* Linux Config */
    final String propertiesFile = new File("").getAbsolutePath() + "/src/properties/configuration.properties";
    final String driver = "org.postgresql.Driver";
    
    
    public GestorConexionImpl() throws RemoteException, ExceptionBD {
        conectaBD();
    }

    public GestorConexionImpl(int paramInt) throws RemoteException, ExceptionBD {
        super(paramInt);
        conectaBD();
    }

    
    private void conectaBD() throws ExceptionBD {
        try {
            Class.forName("org.postgresql.Driver");
            Properties localProperties = new Properties();
            localProperties.load(new FileInputStream(this.propertiesFile));
            String str1 = localProperties.getProperty("url");
            String str2 = localProperties.getProperty("username");
            String str3 = localProperties.getProperty("password");
            
            
            this.conexion = DriverManager.getConnection(str1, str2, str3);
        } catch (ClassNotFoundException localClassNotFoundException) {
            //Servidor.diplay(localClassNotFoundException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.JDBCKO"));
            
        } catch (FileNotFoundException localFileNotFoundException) {
           // Servidor.diplay(localFileNotFoundException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.ConfigurationKO"));
        } catch (IOException localIOException) {
            // Servidor.diplay(localIOException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.PropertiesKO"));
        } catch (SQLException localSQLException) {
            // Servidor.diplay(localSQLException.getMessage());
            throw new ExceptionBD(TDSLanguageUtils.getMessage("Aplication.BBDDKO"));
        } catch (Exception localException) {
           // Servidor.diplay(localException.getMessage());
            throw new ExceptionBD(localException.getMessage());
        }
    }
    
    
    
    
   // @Override
    public int usuariLogin(String username, String password) throws RemoteException, SQLException {
        int id = 0;
        Statement st = null;
        ResultSet result = null;
        
        
        //String str = "SELECT \"Usuari\".\"ID\" FROM \"Usuari\" WHERE \"usuari\" = '"+username+"' AND \"contrasenya\" = '"+password+"' AND \"actiu\" = TRUE LIMIT 1";
        //  String str = "SELECT * FROM \"Usuari\"";         
        try {
            st = this.conexion.createStatement(1004,1008);
            String str ="SELECT \"ID\" FROM \"Usuari\" WHERE usuari = '"+username+"' AND contrasenya = '"+password+"' AND \"actiu\" = TRUE LIMIT 1;";
            
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            
            int q = result.getFetchSize();
            System.out.println("Tamany : "+q);
            System.out.println(str);
            
           /* Comprovació del nombre de files
            Si = 0 -> No s'ha trobat
            Si = 1 -> OK 
            */ 
            int rowCount = result.last() ? result.getRow() : 0;
            result.beforeFirst();
            
            System.out.println("Resultado : "+rowCount);
            
            if(rowCount==1) {
                while(result.next()) {
                    id = result.getInt("ID");
                }
            } 
        } catch (SQLException e) {
            //System.out.println(str);
            e.printStackTrace();
        }
        return id ;
    }
    
    //@Override
    public String saludo() {
        return "Hola campeon!";
    }
    
    //@Override
    public Usuari loadUsuari(int id) throws RemoteException, SQLException {
        Usuari u = new Usuari();
        u.setID(0);
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str ="SELECT * FROM \"Usuari\" WHERE \"ID\" = '"+id+"' LIMIT 1;";
            
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println(str);
            
            while(result.next()) {
                System.out.println("ID "+result.getInt("id")+" Username "+result.getString("usuari"));
                u.setID(result.getInt("id"));
                u.setTaller(this.loadTaller(result.getInt("taller")));
                u.setUsuari(result.getString("usuari"));
                u.setPerfil(this.loadPerfil(result.getInt("perfil")));
                u.setContrasenya(result.getString("contrasenya"));
                u.setActiu(result.getBoolean("actiu"));
                u.setDataAlta(result.getDate("dataalta"));
                u.setDataModificacio(result.getDate("datamodificacio"));
                u.setDataBaixa(result.getDate("databaixa"));
                u.setReparacionsAssignades(result.getInt("reparacionsassignades")); 
                u.setNom(result.getString("nom"));
                u.setCognom1(result.getString("cognom1"));
                u.setCognom2(result.getString("cognom2"));
                u.setEsborrat(result.getBoolean("esborrat"));
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return u;
    }
    
    
    public Usuari loadCapTaller(int id) throws RemoteException, SQLException {
        Usuari u = new Usuari();
        u.setID(0);
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT * FROM \"Usuari\" WHERE \"id\" = '"+id+"' LIMIT 1";
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println(str);
            
            while(result.next()) {
                System.out.println("ID "+result.getInt("id")+" Username "+result.getString("usuari"));
                u.setID(result.getInt("id"));
                u.setUsuari(result.getString("usuari"));
                u.setPerfil(this.loadPerfil(result.getInt("perfil")));
                u.setContrasenya(result.getString("contrasenya"));
                u.setNom(result.getString("nom"));
                u.setCognom1(result.getString("cognom1"));
                u.setCognom2(result.getString("cognom2"));
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return u;
    }
    
    
    
    
    
    @Override
    public void saveUsuari(Usuari u) throws RemoteException, SQLException {
    
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
          //  String str = "UPDATE \"Usuari\" SET nom = '"+u.getNom()+"', cognom1 = '"+u.getCognom1()+"', cognom2 = '"+u.getCognom2()+"', usuari = '"+u.getUsuari()+"', actiu = '"+u.getActiu()+"', dataModificacio = 'NOW()' WHERE \"ID\" = '"+u.getID()+"' ";

            String pass;
            if(u.getUpdateContrasenya()==true){
                pass = ", \"contrasenya\" = '"+u.getContrasenya()+"'";
            } else {
                pass = "";
            }
            
            
            String str = "UPDATE \"Usuari\" SET nom = '"+u.getNom()+"', cognom1 = '"+u.getCognom1()+"', cognom2 = '"+u.getCognom2()+"', actiu = '"+u.getActiu()+"', usuari = '"+u.getUsuari()+"', \"datamodificacio\" = 'NOW()', \"taller\" = '"+u.getTaller().getId()+"', \"perfil\" = '"+u.getPerfil().getId()+"' "+pass+" WHERE \"id\" = '"+u.getID()+"' ";
            st.executeUpdate(str);
            
          //  Servidor.diplay("ConsultaBD: " + str);
            System.out.println("Query de update "+str);
                   
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        
    }
    
   
    @Override
    public Object[][] getLlistaUsuarisTaula() throws RemoteException, SQLException {
      Object [][] a =  { };
      return a;
        
    }     
        
   public ArrayList<Usuari> getLlistaUsuaris() throws RemoteException, SQLException {
       ArrayList llista = new ArrayList();
        
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT * FROM \"public\".\"usuari\"";
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println(str);
            
            while(result.next()) {
                Usuari u = new Usuari();
                System.out.println("ID "+result.getInt("id")+" Username "+result.getString("usuari"));
                u.setID(result.getInt("id"));
                u.setNom(result.getString("nom"));
                u.setCognom1(result.getString("cognom1"));
                u.setCognom2(result.getString("cognom2"));
                u.setUsuari(result.getString("usuari"));
                u.setPerfil(this.loadPerfil(result.getInt("perfil")));
                u.setContrasenya(result.getString("contrasenya"));
                u.setActiu(result.getBoolean("actiu"));
                u.setDataAlta(result.getDate("dataalta"));
                u.setDataModificacio(result.getDate("datamodificacio"));
                u.setDataBaixa(result.getDate("databaixa"));
                u.setReparacionsAssignades(result.getInt("reparacionsassignades")); 
                u.setEsborrat(result.getBoolean("esborrat"));
                System.out.println("Aqui "+u.getNom());
                llista.add(u);
                
                
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return llista;
    }
    
   
    public ArrayList<Taller> getLlistaTallers() throws RemoteException, SQLException {
            ArrayList llista = new ArrayList();
            Statement st = null;
            ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT * FROM \"public\".\"taller\"";
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println(str);
            
            while(result.next()) {
                
                Taller t = new Taller();
                t.setId(result.getInt("id"));
                t.setNom(result.getString("nom"));
                t.setCif(result.getString("cif"));
                t.setAdresa(result.getString("adreça"));
                t.setCapacitat(result.getInt("capacitat"));
                t.setTelefon(result.getString("telefon"));
                t.setWeb(result.getString("web"));
                t.setActiu(result.getBoolean("actiu"));
                t.setDataApertura(result.getDate("dataapertura"));
                t.setDataModificacio(result.getDate("datamodificacio"));
                t.setDataBaixa(result.getDate("databaixa"));
                t.setEsborrat(result.getBoolean("esborrat"));
                t.setCapTaller(this.loadUsuari(result.getInt("captaller")));
                
                llista.add(t);
                
                
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
            return llista;
    }
    
   
   
     public Taller loadTaller(int id) throws RemoteException, SQLException {
            Statement st = null;
            ResultSet result = null;
            Taller t =  new Taller();
            
        try {
            st = this.conexion.createStatement();
            String str = "SELECT * FROM \"Taller\" WHERE \"ID\" = "+id;
            
            result = st.executeQuery(str);
            
            while(result.next()) {
                
                t.setId(result.getInt("id"));
                t.setCif(result.getString("cif"));
                t.setAdresa(result.getString("adreça"));
                t.setCapacitat(result.getInt("capacitat"));
                //t.setCapTaller(result.getInt("captaller"));
                t.setTelefon(result.getString("telefon"));
                t.setWeb(result.getString("web"));
                t.setActiu(result.getBoolean("actiu"));
                t.setDataApertura(result.getDate("dataapertura"));
                t.setDataModificacio(result.getDate("datamodificacio"));
                t.setDataBaixa(result.getDate("databaixa"));
                t.setNom(result.getString("nom"));
                t.setEsborrat(result.getBoolean("esborrat"));
                t.setCapTaller(this.loadCapTaller(result.getInt("captaller")));
                
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
            return t;
    }
    
    
     
        public ArrayList<Perfil> getLlistaPerfils() throws RemoteException, SQLException {
            ArrayList llista = new ArrayList();
            Statement st = null;
            ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "SELECT * FROM \"public\".\"Perfil\"";
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println(str);
            
            while(result.next()) {
                
                Perfil p = new Perfil();
                p.setId(result.getInt("id"));
                p.setPerfil(result.getString("perfil"));
                p.setDataAlta(result.getDate("dataAlta"));
                p.setDataModificacio(result.getDate("dataModificacio"));
                p.setDataBaixa(result.getDate("dataBaixa"));
                llista.add(p);
               
                
               // System.out.println(result.getString("perfil"));
                
                
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
            return llista;
    }
     
    @Override
   public Perfil loadPerfil(int id) throws RemoteException, SQLException {
            Statement st = null;
            ResultSet result = null;
            Perfil p =  new Perfil();
            
        try {
            st = this.conexion.createStatement();
            String str = "SELECT * FROM \"public\".\"Perfil\" WHERE \"ID\" = "+id;
            result = st.executeQuery(str);
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println(str);
            
            while(result.next()) {
                
                p.setId(result.getInt("ID"));
                p.setPerfil(result.getString("perfil"));
                p.setDataAlta(result.getDate("dataAlta"));
                p.setDataModificacio(result.getDate("dataModificacio"));
                p.setDataBaixa(result.getDate("dataBaixa"));
                
            }
            
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
            return p;
    }
   
    
    @Override
    public boolean checkUsername(String username) throws RemoteException, SQLException {
            Statement st = null;
            ResultSet result = null;
            boolean ok = false;
            System.out.println("AQUI");

        try {
            st = this.conexion.createStatement();
            String str = "SELECT count(\"id\") FROM \"usuari\" WHERE usuari = '"+username+"'";
            result = st.executeQuery(str);
            System.out.println("Busca Username"+ str);
    
           // System.out.println("Resultado : "+result.getInt(1));

            
            while(result.next()){
                System.out.println("Aca "+result.getString(1));
                if(result.getString(1).equals("1")){
                       System.out.println("Dentro");
                    ok = true;
                }
            }
            //ok = true;
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println("checkUsername "+str);
        } catch (SQLException e) {
            //System.out.println(str);
            e.printStackTrace();
        }
        
           System.out.println("Salir");
            return ok;
    }
    
    /* Create Usuari */
    
         public void createUsuari(Usuari u) throws RemoteException, SQLException {
             System.out.println("Creando el usuario");
           
                try {
                    Statement st = null;
                    //ResultSet res = null;
                    st = this.conexion.createStatement();
                    String str = "INSERT INTO \"public\".\"usuari\" "
                            + "(\"id\",\"taller\",\"usuari\",\"contrasenya\",\"actiu\",\"dataalta\","
                            + "\"datamodificacio\",\"databaixa\",\"reparacionsassignades\",\"nom\",\"cognom1\","
                            + "\"cognom2\",\"perfil\", \"esborrat\")" 
                            + "VALUES (nextval('\"usuari_id_seq\"'::regclass),'"+u.getTaller().getId()+"','"+u.getUsuari()+"',"
                            + "'"+u.getContrasenya()+"',"+u.getActiu()+",now(),NULL,NULL,0,'"+u.getNom()+"','"+u.getCognom1()+"',"
                            + "'"+u.getCognom2()+"','"+u.getPerfil().getId()+"', 'false')";
                     st.executeUpdate(str);
                     System.out.println("Insertando usuario"+ str);

                }  catch (SQLException e) {
                    e.printStackTrace();
                } 
         
         }
    
     public void delUser(int id) throws RemoteException, SQLException {
         
         try {
                    Statement st = null;
                    //ResultSet res = null;
                    st = this.conexion.createStatement();
                    String str = "UPDATE \"usuari\" SET actiu = 'false', esborrat = 'true' WHERE \"id\" = '"+id+"'";
                     st.executeUpdate(str);
                     System.out.println("Cambiando estado del usuario"+ str);

                }  catch (SQLException e) {
                    e.printStackTrace();
                } 
     }  

    public void saveTaller(Taller t) throws RemoteException, SQLException {
    
        Statement st = null;
        ResultSet result = null;
        
        try {
            st = this.conexion.createStatement();
            String str = "UPDATE \"taller\" SET nom = '"+t.getNom()+"', cif = '"+t.getCif()+"', "
                    + "adreça = '"+t.getAdresa()+"', captaller = '"+t.getCapTaller().getID()+"', "
                    + "actiu = '"+t.getActiu()+"', telefon = '"+t.getTelefon()+"', web = '"+t.getWeb()+"', "
                    + "dataModificacio = 'NOW()' WHERE \"id\" = '"+t.getId()+"' ";

            System.out.println(str);
            st.executeUpdate(str);
          //  Servidor.diplay("ConsultaBD: " + str);
                 
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
       
    }


    @Override
     public void createTaller(Taller t) throws RemoteException, SQLException {
         
         System.out.println("Creando el usuario");
           
                try {
                    Statement st = null;
                    st = this.conexion.createStatement();
                    String str = "INSERT INTO \"public\".\"taller\" "
                            + "(\"id\",\"nom\",\"cif\",\"adreça\",\"capacitat\",\"captaller\","
                            + "\"telefon\",\"web\",\"actiu\", \"esborrat\", \"dataapertura\") "
                            + "VALUES (nextval('Taller_id_seq'::regclass),'"+t.getNom()+"','"+t.getCif()+"', '"+t.getAdresa()+"', "
                            + "'"+t.getCapacitat()+"',"+t.getCapTaller().getID()+", '"+t.getTelefon()+"', '"+t.getWeb()+"', "
                            + "'"+t.getActiu()+"','false', NOW())";
                     st.executeUpdate(str);
                     System.out.println("Insertando taller "+ str);

                }  catch (SQLException e) {
                    e.printStackTrace();
                } 
  
     }

    @Override
     public boolean checkCif(String cif) throws RemoteException, SQLException {
            Statement st = null;
            ResultSet result = null;
            boolean ok = false;
            
        try {
            st = this.conexion.createStatement();
            String str = "SELECT count(\"id\") FROM \"taller\" WHERE cif = '"+cif+"'";
            result = st.executeQuery(str);
            System.out.println("Busca cif"+ str);
    
            while(result.next()){
                System.out.println("Aca "+result.getString(1));
                if(result.getString(1).equals("1")){
                       System.out.println("Dentro");
                    ok = true;
                }
            }
            //Servidor.diplay("ConsultaBD: " + str);
            System.out.println("checkCif "+str);
        } catch (SQLException e) {
            e.printStackTrace();
        }
            return ok;
    
    }
    
        public void delTaller(int id) throws RemoteException, SQLException {
         
         try {
                    Statement st = null;
                    //ResultSet res = null;
                    st = this.conexion.createStatement();
                    String str = "UPDATE \"taller\" SET actiu = 'false', esborrat = 'true' WHERE \"id\" = '"+id+"'";
                     st.executeUpdate(str);

                }  catch (SQLException e) {
                    e.printStackTrace();
                } 
     }  

       public ArrayList<Taller> cercarTallers(String text) throws RemoteException, SQLException {
           
           ArrayList<Taller> cerca = new ArrayList<Taller>();
           
           
            Statement st = null;
            ResultSet result = null;
            boolean ok = false;
            
        try {
            st = this.conexion.createStatement();

            String query_text = "";
            if(text.length()>0) {
                query_text = "and nom like '%"+text+"%' ";
            }
            
            
            String str = "select * FROM taller where id > 0 "+query_text;
            
            
            System.out.println("Query : "+str);
            
            result = st.executeQuery(str);
            while(result.next()){
                Taller t = new Taller();
                t.setId(result.getInt("id"));
                t.setCif(result.getString("cif"));
                t.setAdresa(result.getString("adreça"));
                t.setCapacitat(result.getInt("capacitat"));
                t.setTelefon(result.getString("telefon"));
                t.setWeb(result.getString("web"));
                t.setActiu(result.getBoolean("actiu"));
                t.setDataApertura(result.getDate("dataapertura"));
                t.setDataModificacio(result.getDate("datamodificacio"));
                t.setDataBaixa(result.getDate("databaixa"));
                t.setNom(result.getString("nom"));
                t.setEsborrat(result.getBoolean("esborrat"));
                t.setCapTaller(this.loadCapTaller(result.getInt("captaller")));
                cerca.add(t);

            } 
            //Servidor.diplay("ConsultaBD: " + str);
  
        } catch (SQLException e) {
            e.printStackTrace();
        }
           return cerca;
       } 
       
    
       
       public ArrayList<Usuari> cercarUsuaris(String text, Taller t, Perfil p) throws RemoteException, SQLException {
           
           ArrayList<Usuari> cerca = new ArrayList<Usuari>();
            Statement st = null;
            ResultSet result = null;
            boolean ok = false;
            
        try {
            st = this.conexion.createStatement();

            String query_text = "";
            if(text.length()>0) {
                query_text = "and usuari like '%"+text+"%' ";
            }
            
            String query_taller = "";
            if(t.getId()>0) {
                query_taller = "and taller = '"+t.getId()+"' ";
            }
            
            
            String query_perfil = "";
            if(p.getId()>0) {
                query_perfil = "and perfil = '"+p.getId()+"'";
            }
            
            String str = "select * FROM usuari where id > 0 "+query_text+query_taller+query_perfil;;
            
            
            System.out.println("Query : "+str);
            
            result = st.executeQuery(str);
            while(result.next()){
                Usuari u = new Usuari();
                System.out.println("Cerca "+result.getInt("id")+" Username "+result.getString("usuari"));
                u.setID(result.getInt("id"));
                u.setUsuari(result.getString("usuari"));
                u.setPerfil(this.loadPerfil(result.getInt("perfil")));
                u.setContrasenya(result.getString("contrasenya"));
                u.setActiu(result.getBoolean("actiu"));
                u.setDataAlta(result.getDate("dataalta"));
                u.setDataModificacio(result.getDate("datamodificacio"));
                u.setDataBaixa(result.getDate("databaixa"));
                u.setReparacionsAssignades(result.getInt("reparacionsassignades")); 
                u.setEsborrat(result.getBoolean("esborrat"));

                cerca.add(u);

            } 
            //Servidor.diplay("ConsultaBD: " + str);
            
        } catch (SQLException e) {
            e.printStackTrace();
        } 
           return cerca; 
       } 
       
 }
